<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
	<module name="project" >
		<statement name="exists_by_id" resultType="bool">
			<![CDATA[
			select exists(select project_id from project where project_id = #project_id#);
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="validate_duplicated_project" resultType="bool" >
			<![CDATA[
			select exists(
			select project_id from project
			where `name` = #project_name#
			and project_id <> #project_id#)
			]]>
			<parameter name="project_name" type="string" />
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_by_id" resultType="table">
			<![CDATA[
		select
			a.project_id as projectId,
			ifnull(b1.`key`,'') as industry,
			ifnull(b2.`key`,'') as stage,
			a.end_date endDate,
			a.start_date startDate,
			a.primary_key1 primaryKey1,
			a.primary_key2 primaryKey2,
			a.primary_key3 primaryKey3,
			a.index_key1 indexKey1,
			a.index_key2 indexKey2,
			a.index_key3 indexKey3,
			a.index_key4 indexKey4,
			ifnull(b.`key`,'') as level,
			a.`name`,
			case a.`type` 
			when '1' then (select b16.`key` from sys_code b16 where b16.key=a.style and b16.`type`= 12)
			when '2' then (select b16.`key` from sys_code b16 where b16.key=a.style and b16.`type`= 5)
			when '3' then (select b16.`key` from sys_code b16 where b16.key=a.style and b16.`type`= 5)
			end as style,
			ifnull(b8.`key`,'') as property,
			ifnull(b9.`key`,'') as purpose,
			ifnull(b10.`key`,'') as secret,
			ifnull(b11.`key`,'') as research,
			ifnull(b12.`key`,'') as technology,
			ifnull(b13.`key`,'') as  transfer,
			a.`type`,
			a.user_id userId,
			a.dep_id depId,
			a.`status`,
			a.approve_code as approveCode,
			approve_date as approveDate,
			recommend_company as recommendCompany,
			b.`value` as levelValue,
			b1.`value` as industryValue,
			b2.`value` as stageValue,
			b3.`value` as indexKey1Value,
			b4.`value` as indexKey2Value,
			b5.`value` as indexKey3Value,
			b6.`value` as indexKey4Value,
			b8.`value` as propertyValue,
			b9.`value` as purposeValue,
			b10.`value` as secretValue,
			b11.`value` researchValue,
			b12.`value` as technologyValue,
			b13.`value` as transferValue,
			c.real_name as userName,
			case a.`type` 
			when '1' then (select b14.`value` from sys_code b14 where b14.key=a.style and b14.`type`= 12)
			when '2' then (select b14.`value` from sys_code b14 where b14.key=a.style and b14.`type`= 5)
			when '3' then (select b14.`value` from sys_code b14 where b14.key=a.style and b14.`type`= 5)
			end as styleValue,
			b15.`value` as depName,
			a.`year`
			from project as a 
			left join sys_code as b on a.level=b.key and b.type=26
			left join sys_code as b1 on a.industry=b1.key and b1.type=22
			left join sys_code as b2 on a.stage=b2.key  and b2.type=23
			left join sys_code as b3 on a.index_key1=b3.key and b3.type=41
			left join sys_code as b4 on a.index_key2=b4.key and b4.type=41
			left join sys_code as b5 on a.index_key3=b5.key and b5.type=42
			left join sys_code as b6 on a.index_key4=b6.key and b6.type=42
			left join sys_code as b8 on a.property=b8.key and b8.type=19
			left join sys_code as b9 on a.purpose=b9.key and b9.type=36
			left join sys_code as b10 on a.secret=b10.key and b10.type=18
			left join sys_code as b11 on a.research=b11.key and b11.type=35
			left join sys_code as b12 on a.technology=b12.key and b12.type=29
			left join sys_code as b13 on a.transfer=b13.key and b13.type=39
			left join sys_code as b15 on a.dep_id=b15.key and b15.type=40
			left join sec_user as c on a.user_id=c.user_id
			where project_id=#project_id# 
			order by convert(a.`name` using gbk), a.`year`
				]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name ="find_project_is_saved" resultType="table">
			<![CDATA[
			select
		    p.`type` as projectType,
			p.is_saved as projectSaved,
			pe1.is_saved as establishSaved,
			pi.is_saved as iprsSaved,
			pb1.is_saved as briefSaved,
			pc.is_saved as certSaved,
			po.is_saved as orgSaved,
			pe2.is_saved as evalSaved,
			pb2.is_saved as bonusSaved,
			(select exists(select pcp.project_id from project_coop_person  as pcp where pcp.is_saved=1 and pcp.project_id=#project_id#)) as personSaved
			from project as p 
			left join project_establish as pe1 on p.project_id = pe1.project_id
			left join project_iprs as pi on p.project_id = pi.project_id
			left join project_brief as pb1 on p.project_id = pb1.project_id
			left join project_cert as pc on p.project_id = pc.project_id
			left join project_org as po on p.project_id = po.project_id
			left join project_eval as pe2 on p.project_id = pe2.project_id
			left join project_bonus as pb2 on p.project_id = pb2.project_id
			where p.project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete" commandType="sp">
			<![CDATA[delete_project]]>
			<parameter name="projectID" type="string" />
		</statement>

		<statement name="change_status">
			<![CDATA[
			update project set status=#new_status# where project_id=#project_id#
			]]>
			<parameter name="new_status" type="int" />
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_related_entities_by_project_id" resultType="table">
			<![CDATA[
			select
			entity_id as entityId,
			`name`
			from project_entities 
			where project_id=#project_id#
			order by convert(`name` using gbk)
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_related_entities_by_project_apply_id" resultType="table">
			<![CDATA[
			select
			pe.entity_id as entityId,
			pe.`name`
			from project_entities as pe 
			join project_apply as pa on pe.project_id = pa.project_id
			where pa.project_apply_id=#project_apply_id#
			and pe.project_id = pe.ref_project_id
			order by convert(pe.`name` using gbk)
			]]>
			<parameter name="project_apply_id" type="string" />
		</statement>

		<statement name="find_related_entities" resultType="table" allowPaging="true">
			<![CDATA[
			select
			pe.project_id as projectId,
			pe.entity_id as entityId,
			pe.`name`,
			pe.`type`,
			p.dep_id as depId,			
			refp.project_id as refProjectId,
		    refp.`name` as refProjectName,	
			refp.`status` as refProjectStatus,
			pe.project_id = pe.ref_project_id as isOriginal,
			(select exists(
			select entity_id from 
			entity_auth  as ea
			where ea.dep_id=#cur_dep_id# and ea.entity_id=pe.entity_id and ea.is_confirm=1)
			) as IsDepAuthOk,
			(select exists(
			select user_id from 
			entity_auth_item as eai 
			where eai.dep_id=#cur_dep_id# and user_id =#user_id# 
			and eai.entity_id=pe.entity_id and eai.is_confirm=1)
			) as IsAuthOk,
			(select p.dep_id=#cur_dep_id#) as IsDepOk
			from project_entities as pe
			join project as p on pe.project_id = p.project_id
			join project as refp on pe.ref_project_id = refp.project_id
			where pe.project_id=#project_id#
			order by convert(pe.`name` using gbk)
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="cur_dep_id" type="string" />
		</statement>

		<statement name="find_related_entities_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_entities as pe
			join project as p on pe.project_id = p.project_id
			where pe.project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="cur_dep_id" type="string" />
		</statement>

		<statement name="create">
			<![CDATA[
			insert into project
			(
			project_id,
			industry,
			stage,
			end_date,
			start_date,
			primary_key1,
			primary_key2,
			primary_key3,
			index_key1,
			index_key2,
			index_key3,
			index_key4,
			level,
			name,
			style,
			property,
			purpose,
			secret,
			research,
			technology,
			transfer,
			type,
			user_id,
			dep_id,
			status,
			approve_code,
			approve_date,
			recommend_company,
			year,
			is_saved
			)
			values(
			#project_id#,
			#industry#,
			#stage#,
			#end_date#,
			#start_date#,
			#primary_key1#,
			#primary_key2#,
			#primary_key3#,
			#index_key1#,
			#index_key2#,
			#index_key3#,
			#index_key4#,
			#level#,
			#name#,
			#style#,
			#property#,
			#purpose#,
			#secret#,
			#research#,
			#technology#,
			#transfer#,
			#type#,
			#user_id#,
			#dep_id#,
			#status#,
			#approve_code#,
			#approve_date#,
			#recommend_company#,
			#year#,
			#is_saved#
			)
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="industry" type="string" />
			<parameter name="stage" type="string" />
			<parameter name="end_date" type="datetime" />
			<parameter name="start_date" type="datetime"  />
			<parameter name="primary_key1" type="string" />
			<parameter name="primary_key2" type="string" />
			<parameter name="primary_key3" type="string" />
			<parameter name="index_key1" type="string" />
			<parameter name="index_key2" type="string" />
			<parameter name="index_key3" type="string" />
			<parameter name="index_key4" type="string" />
			<parameter name="level" type="string" />
			<parameter name="name" type="string" />
			<parameter name="style" type="string" />
			<parameter name="property" type="string" />
			<parameter name="purpose" type="string" />
			<parameter name="secret" type="string" />
			<parameter name="research" type="string" />
			<parameter name="technology" type="string" />
			<parameter name="transfer" type="string" />
			<parameter name="type" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="status" type="int" />
			<parameter name="approve_code" type="string" />
			<parameter name="approve_date" type="datetime" />
			<parameter name="recommend_company" type="string" />
			<parameter name="year" type="int" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="update">
			<![CDATA[
			update project
			set project_id=#project_id#,
			industry=#industry#,
			stage=#stage#,
			end_date=#end_date#,
			start_date=#start_date#,
			primary_key1=#primary_key1#,
			primary_key2=#primary_key2#,
			primary_key3=#primary_key3#,
			index_key1=#index_key1#,
			index_key2=#index_key2#,
			index_key3=#index_key3#,
			index_key4=#index_key4#,
			level=#level#,
			name=#name#,
			style=#style#,
			property=#property#,
			purpose=#purpose#,
			secret=#secret#,
			research=#research#,
			technology=#technology#,
			transfer=#transfer#,
			type=#type#,
			user_id=#user_id#,
			dep_id=#dep_id#,
			status=#status#,
			approve_code=#approve_code#,
			approve_date=#approve_date#,
			recommend_company=#recommend_company#,
			year=#year#,
			is_saved=#is_saved#
			where project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="industry" type="string" />
			<parameter name="stage" type="string" />
			<parameter name="end_date" type="datetime" />
			<parameter name="start_date" type="datetime" />
			<parameter name="primary_key1" type="string" />
			<parameter name="primary_key2" type="string" />
			<parameter name="primary_key3" type="string" />
			<parameter name="index_key1" type="string" />
			<parameter name="index_key2" type="string" />
			<parameter name="index_key3" type="string" />
			<parameter name="index_key4" type="string" />
			<parameter name="level" type="string" />
			<parameter name="name" type="string" />
			<parameter name="style" type="string" />
			<parameter name="property" type="string" />
			<parameter name="purpose" type="string" />
			<parameter name="secret" type="string" />
			<parameter name="research" type="string" />
			<parameter name="technology" type="string" />
			<parameter name="transfer" type="string" />
			<parameter name="type" type="string" />
			<parameter name="user_id" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="status" type="int" />
			<parameter name="approve_code" type="string" />
			<parameter name="approve_date" type="datetime" />
			<parameter name="recommend_company" type="string" />
			<parameter name="year" type="int" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find" allowPaging="true" resultType="table">
			<![CDATA[
			select
			a.project_id as projectId,
			a.industry,
			a.stage,
			a.end_date endDate,
			a.start_date startDate,
			a.primary_key1 primaryKey1,
			a.primary_key2 primaryKey2,
			a.primary_key3 primaryKey3,
			a.index_key1 indexKey1,
			a.index_key2 indexKey2,
			a.index_key3 indexKey3,
			a.index_key4 indexKey4,
			ifnull(k.`key`,'') as `level`,
			a.`name`,
			a.style,
			a.property,
			a.purpose,
			a.secret,
			a.research,
			a.technology,
			a.transfer,
			a.`type`,
			d.`name` as  ownerName,
			a.user_id userId,
			a.dep_id depId,
			ifnull(k3.`key`,'') as `status`,
			k3.`value` as statusValue,
			a.approve_code as approveCode,
			approve_date as approveDate,
			recommend_company as recommendCompany,
			k.`value` as levelValue,
			c.all_money as allMoney,
			k4.`value` as prizeName,
			ifnull(k2.`key`,'') as `source`,
			k2.`value` as sourceValue,
			k5.`value` as depName,
			a.`year`
			from project as a left join project_bonus as b
			on a.project_id=b.project_id
			left join project_establish c on a.project_id=c.project_id
			left join project_org d on a.project_id = d.project_id
			left join sys_code as k on a.level=k.key and k.type=26
			left join sys_code as k2 on c.source=k2.key and k2.type=38
			left join sys_code as k3 on a.status=k3.key and k3.type=27
			left join sys_code as k4 on b.name=k4.key and k4.type=8
			left join sys_code as k5 on a.dep_id=k5.key and k5.type=40	
				]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="name">
					<![CDATA[
					a.`name` like concat('%', #name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="ownerName">
					<![CDATA[
					d.`name` like concat('%', #ownerName#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="min_start_date">
					<![CDATA[
					a.start_date>=#min_start_date#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_start_date">
					<![CDATA[
					a.start_date<=#max_start_date#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="primary_key">
					<![CDATA[
					(a.primary_key1 like concat('%',#primary_key#,'%')
					or a.primary_key2 like concat('%',#primary_key#,'%')
					or a.primary_key3 like concat('%',#primary_key#,'%'))
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="level">
					<![CDATA[
					a.`level`=#level#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="industry">
					<![CDATA[
					a.industry=#industry#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="source">
					<![CDATA[
					c.`source`=#source#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="bonus_name">
					<![CDATA[
					k4.`value` like concat('%', #bonus_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="type">
					<![CDATA[
					a.`type`=#type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					a.dep_id=#dep_id#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="year">
					<![CDATA[
					a.year=#year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="min_all_money">
					<![CDATA[
					c.all_money>=#min_all_money#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_all_money">
					<![CDATA[
					c.all_money<=#max_all_money#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="status">
					<![CDATA[					
					a.`status` in ($status$)
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			order by convert(a.`name` using gbk), a.`year`
			]]>
			<parameter name="name" type="string" matchType="contains" />
			<parameter name="ownerName" type="string"  matchType="contains"/>
			<parameter name="min_start_date" type="date" />
			<parameter name="max_start_date" type="endDate" />
			<parameter name="primary_key" type="string" matchType="contains"/>
			<parameter name="level" type="string" />
			<parameter name="industry" type="string" />
			<parameter name="source" type="string" />
			<parameter name="bonus_name" type="string" matchType="contains"/>
			<parameter name="type" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="year" type="string" />
			<parameter name="min_all_money" type="money" />
			<parameter name="max_all_money" type="money" />
			<parameter name ="status"  type="string"/>
		</statement>

		<statement name="find_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project as a left join project_bonus as b
			on a.project_id=b.project_id
			left join project_establish c on a.project_id=c.project_id
			left join project_org d on a.project_id = d.project_id
			left join sys_code as k on a.level=k.key and k.type=26
			left join sys_code as k2 on c.source=k2.key and k2.type=38
			left join sys_code as k3 on a.status=k3.key and k3.type=27
			left join sys_code as k4 on b.name=k4.key and k4.type=8	
			left join sys_code as k5 on a.dep_id=k5.key and k5.type=40	
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="name">
					<![CDATA[
					a.`name` like concat('%', #name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="ownerName">
					<![CDATA[
					d.`name` like concat('%', #ownerName#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="min_start_date">
					<![CDATA[
					a.start_date>=#min_start_date#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_start_date">
					<![CDATA[
					a.start_date<=#max_start_date#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="primary_key">
					<![CDATA[
					(a.primary_key1 like concat('%',#primary_key#,'%')
					or a.primary_key2 like concat('%',#primary_key#,'%')
					or a.primary_key3 like concat('%',#primary_key#,'%'))
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="level">
					<![CDATA[
					a.`level`=#level#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="industry">
					<![CDATA[
					a.industry=#industry#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="source">
					<![CDATA[
					c.`source`=#source#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="bonus_name">
					<![CDATA[
					k4.`value` like concat('%', #bonus_name#, '%')
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="type">
					<![CDATA[
					a.`type`=#type#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					a.dep_id=#dep_id#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="year">
					<![CDATA[
					a.year=#year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="min_all_money">
					<![CDATA[
					c.all_money>=#min_all_money#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_all_money">
					<![CDATA[
					c.all_money<=#max_all_money#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="status">
					<![CDATA[					
					a.`status` in ($status$)
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="name" type="string" matchType="contains" />
			<parameter name="ownerName" type="string" matchType="contains"/>
			<parameter name="min_start_date" type="date" />
			<parameter name="max_start_date" type="endDate" />
			<parameter name="primary_key" type="string" matchType="contains"/>
			<parameter name="level" type="string" />
			<parameter name="industry" type="string" />
			<parameter name="source" type="string" />
			<parameter name="bonus_name" type="string" matchType="contains"/>
			<parameter name="type" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="year" type="string" />
			<parameter name="min_all_money" type="money" />
			<parameter name="max_all_money" type="money" />
			<parameter name ="status"  type="string"/>
		</statement>

		<statement name="find_brief_by_id" resultType="table">
			<![CDATA[
			select
			project_brief_id as projectBriefId,
			project_id as projectId,
			content,
			summary
			from project_brief
			where project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_brief">
			<![CDATA[
			delete from project_brief where project_id = #project_id#;
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_brief">
			<![CDATA[
			insert into project_brief
			(
			project_brief_id,
			project_id,
			content,
			summary,
			is_saved
			)values
			(
			#project_brief_id#,
			#project_id#,
			#content#,
			#summary#,
			#is_saved#
			)
			]]>
			<parameter name="project_brief_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="content" type="string" />
			<parameter name="summary" type="string" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_eval_by_id" resultType="table">
			<![CDATA[
			select
			a.project_eval_id as projectEvalId,
			a.project_id as projectId,
			case c.`type`
			when 1 then b1.`key`
			when 2 then b2.`key`
			when 3 then b2.`key`
			else ''
			end as method,
			a.company,
			a.`date`,
			c.`type`,
			case c.`type`
			when 1 then b1.`value`
			when 2 then b2.`value`
			when 3 then b2.`value`
			else ''
			end as methodValue,
			d.`code` as code
			from project_eval as a
			left join project c on a.project_id=c.project_id
			left join project_cert d on a.project_id = d.project_id
			left join sys_code b1 on a.method=b1.`key` and b1.`type`=10
			left join sys_code b2 on a.method=b2.`key` and b2.`type`=6
			where a.project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_eval">
			<![CDATA[
			delete from project_eval where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_eval">
			<![CDATA[
			insert into project_eval
			(
			project_eval_id,
			project_id,
			method,
			company,
			date,
			is_saved
			)
			values
			(
			#project_eval_id#,
			#project_id#,
			#method#,
			#company#,
			#date#,
			#is_saved#
			)
			]]>
			<parameter name="project_eval_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="method" type="string" />
			<parameter name="company" type="string" />
			<parameter name="date" type="datetime" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_org_by_id" resultType="table">
			<![CDATA[
				select
			a.project_org_id as projectOrgId,
			a.project_id as projectId,
			a.name,
			a.dep_id as depId,
			a.code,
			a.address,
			a.telephone,
			a.website,
			a.postcode,
			a.faxcode,
		    ifnull(b1.`key`,'') as type,
			ifnull(b2.`key`,'') as property,
			ifnull(b3.`key`,'') as province,
			ifnull(b4.`key`,'') as parent,
			a.contract_name as contractName,
			a.contract_telephone as contractTelephone,
			a.contract_email as contractEmail,
			a.transfer_name as transferName,
			a.transfer_telephone as transferTelephone,
			a.transfer_email as transferEmail,
			a.is_sciencecompany as isScienceCompany,
			b.value as departmentValue,
			b1.value as typeValue,
			b2.value as propertyValue,
			b3.value as provinceValue,
			b4.value as parentValue
			from project_org a
			left join sys_code b on a.dep_id=b.key	and b.type=40
			left join sys_code b1 on a.type=b1.key	and b1.type=2
			left join sys_code b2 on a.property=b2.key	and b2.type=1
			left join sys_code b3 on a.province=b3.key	and b3.type=3
			left join sys_code b4 on a.parent=b4.key	and b4.type=43
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_org">
			<![CDATA[
			delete from project_org where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_org">
			<![CDATA[
			 insert into project_org
			(
			project_org_id,
			project_id,
			name,
			dep_id ,
			code,
			address,
			telephone,
			website,
			postcode,
			faxcode,
			type,
			property,
			province,
			parent,
			contract_name,
			contract_telephone,
			contract_email,
			transfer_name,
			transfer_telephone,
			transfer_email,
			is_sciencecompany,
			is_saved
			)
			values
			(
			#project_org_id#,
			#project_id#,
			#name#,
			#dep_id#,
			#code#,
			#address#,
			#telephone#,
			#website#,
			#postcode#,
			#faxcode#,
			#type#,
			#property#,
			#province#,
			#parent#,
			#contract_name#,
			#contract_telephone#,
			#contract_email#,
			#transfer_name#,
			#transfer_telephone#,
			#transfer_email#,
			#is_sciencecompany#,
			#is_saved#
			)
			]]>
			<parameter name="project_org_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="code" type="string" />
			<parameter name="address" type="string" />
			<parameter name="telephone" type="string" />
			<parameter name="website" type="string" />
			<parameter name="postcode" type="string" />
			<parameter name="faxcode" type="string" />
			<parameter name="type" type="string" />
			<parameter name="property" type="string" />
			<parameter name="province" type="string" />
			<parameter name="parent" type="string" />
			<parameter name="contract_name" type="string" />
			<parameter name="contract_telephone" type="string" />
			<parameter name="contract_email" type="string" />
			<parameter name="transfer_name" type="string" />
			<parameter name="transfer_telephone" type="string" />
			<parameter name="transfer_email" type="string" />
			<parameter name="is_sciencecompany" type="bit" />
			<parameter name="is_saved" type="bit" />
		</statement>

		<statement name="find_bonus_by_id" resultType="table">
			<![CDATA[
			select
			a.project_bonus_id as projectBonusId,
			a.project_id as projectId,
			ifnull(b.`key`,'') as grade,
			ifnull(b1.`key`,'') as `name`,
			ifnull(b2.`key`,'') as `level`,
			a.`date`,
			b.`value` as gradeValue,
			b1.`value` as nameValue,
			b2.`value` as levelValue
			from project_bonus a
			left join sys_code b on a.grade=b.key	and b.type=9
			left join sys_code b1 on a.`name`=b1.key	and b1.type=8
			left join sys_code b2 on a.`level`=b2.key	and b2.type=31
			where project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_bonus">
			<![CDATA[
			delete from project_bonus where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_bonus">
			<![CDATA[
			insert into project_bonus
			(
			project_bonus_id,
			project_id,
			grade,
			name,
			level,
			date,
			is_saved
			)
			values
			(
			#project_bonus_id#,
			#project_id#,
			#grade#,
			#name#,
			#level#,
			#date#,
			#is_saved#
			)
			]]>
			<parameter name="project_bonus_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="grade" type="string" />
			<parameter name="name" type="string" />
			<parameter name="level" type="string" />
			<parameter name="date" type="datetime" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_establish_by_id" resultType="table">
			<![CDATA[
			select
			a.project_establish_id as projectEstablishId,
			a.project_id as projectId,
			a.number,
			ifnull(b.`key`,'') as `source`,
			ifnull(b1.`key`,'') as  nationProject,
			a.all_money as allMoney,
			a.nation,
			a.ministry,
			a.local_all as localAll,
			a.province,
			a.city,
			a.country,
			a.fund,
			a.self_owen as selfOwen,
			a.loan,
			a.outside,
			a.other,
			a.`name`,
			b.`value` as sourceValue,
			b1.value as nationProjectValue
			from project_establish as a
			left join sys_code as b on a.`source`=b.`key` and  b.type=38
			left join sys_code as b1 on a.nation_project=b1.`key` and b1.type=4
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_establish">
			<![CDATA[
			delete from project_establish where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_establish">
			<![CDATA[
			insert into project_establish
			(
			project_establish_id,
			project_id,
			number,
			source,
			nation_project,
			all_money,
			nation,
			ministry,
			local_all,
			province,
			city,
			country,
			fund,
			self_owen,
			loan,
			outside,
			other,
			name,
			is_saved
			)
			values
			(
			#project_establish_id#,
			#project_id#,
			#number#,
			#source#,
			#nation_project#,
			#all_money#,
			#nation#,
			#ministry#,
			#local_all#,
			#province#,
			#city#,
			#country#,
			#fund#,
			#self_owen#,
			#loan#,
			#outside#,
			#other#,
			#name#,
			#is_saved#
			)
			]]>
			<parameter name="project_establish_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="number" type="string" />
			<parameter name="source" type="string" />
			<parameter name="nation_project" type="string" />
			<parameter name="all_money" type="decimal" />
			<parameter name="nation" type="decimal" />
			<parameter name="ministry" type="decimal" />
			<parameter name="local_all" type="decimal" />
			<parameter name="province" type="decimal" />
			<parameter name="city" type="decimal" />
			<parameter name="country" type="decimal" />
			<parameter name="fund" type="decimal" />
			<parameter name="self_owen" type="decimal" />
			<parameter name="loan" type="decimal" />
			<parameter name="outside" type="decimal" />
			<parameter name="other" type="decimal" />
			<parameter name="name" type="string"/>
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_project_check" allowPaging="true" resultType="table">
			<![CDATA[
			select
			pc.project_check_id as projectCheckId,
			pc.project_id as projectId,
			p.`name` as  projectName,
			pc.	date,
			pc.`option`,
			pc.`comment`,
			pc.`type`,
			pc.user_id as userId,
			su.`real_name` as userName
			from project_check as pc			
			join sec_user as su on pc.user_id = su.user_id
			join project as p on pc.project_id = p.project_id
			where pc.project_id = #project_id# and pc.`type`=#type#
			order by pc.date desc
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="type" type="int" />
		</statement>

		<statement name="find_project_check_by_id" resultType="table">
			<![CDATA[
			select
			pc.project_check_id as projectCheckId,
			pc.project_id as projectId,
			pc.date,
			pc.`option`,
			pc.`comment`,
			pc.`type`,
			pc.user_id as userId,
			p.`name` as projectName
			from project_check as pc
			join project as p on pc.project_id = p.project_id
			where project_check_id = #project_check_id# and type=#type#		
			]]>
			<parameter name="project_check_id" type="string" />
			<parameter name="type" type="int" />
		</statement>

		<statement name="find_project_check_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_check as pc			
			join sec_user as su on pc.user_id = su.user_id
			join project as p on pc.project_id = p.project_id
			where pc.project_id = #project_id# and pc.`type`=#type#
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="type" type="int" />
		</statement>

		<statement name="delete_project_check">
			<![CDATA[
			delete from project_check where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_project_check">
			<![CDATA[
			insert into project_check
			(
			project_check_id,
			project_id,
			date,
			`option`,
			`comment`,
			type,
			user_id
			)
			values
			(
			#project_check_id#,
			#project_id#,
			#date#,
			#option#,
			#comment#,
			#type#,
			#user_id#
			)
			]]>
			<parameter name="project_check_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="date" type="datetime" />
			<parameter name="option" type="string" />
			<parameter name="comment" type="string" />
			<parameter name="type" type="int" />
			<parameter name="user_id" type="string" />
		</statement>

		<statement name="find_coop_person_by_id" allowPaging="true" resultType="table">
			<![CDATA[
			select
			a.project_coop_person_id as projectCoopPersonId,
			a.project_id as projectId,
			a.seq,
			a.name,
			a.company,
			ifnull(b.`key`,'') as education,
			a.contribute,
			a.birthday,
			a.gender,
			ifnull(b1.`key`,'') as title,
			a.is_return as isReturn,
			b.value as educationValue,
			b1.value as titleValue
			from project_coop_person as a
			left join sys_code as b on a.education=b.key and b.type=32
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_id = #project_id#
			order by a.seq
			]]>
			<parameter name="project_id" type="string" />
		</statement>


		<statement name="change_coop_person_order">
			<![CDATA[
			update  project_coop_person set seq=seq-1			
			where project_id = #project_id#
			and seq > #seq#			
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="seq" type="int" />
		</statement>

		<statement name="find_coop_person" resultType="table">
			<![CDATA[
			select
			a.project_coop_person_id as projectCoopPersonId,
			a.project_id as projectId,
			a.seq,
			a.name,
			a.company,
			ifnull(b.`key`,'') as education,
			a.contribute,
			a.birthday,
			a.gender,
			ifnull(b1.`key`,'') as title,
			a.is_return as isReturn,
			b.value as educationValue,
			b1.value as titleValue
			from project_coop_person as a
			left join sys_code as b on a.education=b.key and b.type=32
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_coop_person_id = #project_coop_person_id#
			]]>
			<parameter name="project_coop_person_id" type="string" />
		</statement>

		<statement name="find_coop_person_by_id_count"  resultType="int">
			<![CDATA[
			select
			count(*)
			from project_coop_person as a
			left join sys_code as b on a.education=b.key and b.type=32
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_max_coop_person_seq_by_id"  resultType="int">
			<![CDATA[
			SELECT 
			ifnull(max(seq),0) as maxSeq 
			FROM project_coop_person
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="update_coop_person">
			<![CDATA[
			update project_coop_person
			set project_coop_person_id=#project_coop_person_id#,
			`name`=#name#,
			company=#company#,
			education=#education#,
			contribute=#contribute#,
			birthday=#birthday#,
			gender=#gender#,
			title=#title#,
			is_return=#is_return#
			where project_coop_person_id = #project_coop_person_id#
			]]>
			<parameter name="project_coop_person_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="company" type="string" />
			<parameter name="education" type="string" />
			<parameter name="contribute" type="string" />
			<parameter name="birthday" type="datetime" />
			<parameter name="gender" type="int" />
			<parameter name="title" type="string" />
			<parameter name="is_return" type="int" />
		</statement>

		<statement name="delete_coop_person">
			<![CDATA[
			delete from project_coop_person where project_coop_person_id = #project_coop_person_id#
			]]>
			<parameter name="project_coop_person_id" type="string" />
		</statement>

		<statement name="delete_coop_person_by_project_id">
			<![CDATA[
			delete from project_coop_person where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="reorder_coop_person" commandType="sp">
			<![CDATA[reorder_project_coop_person]]>
			<parameter name="project_coop_person_ids" type="string" />
			<parameter name="direction" type="int" />
		</statement>

		<statement name="create_coop_person">
			<![CDATA[
			insert into project_coop_person
			(
			project_coop_person_id,
			project_id,
			seq,
			`name`,
			company,
			education,
			contribute,
			birthday,
			gender,
			title,
			is_return,
			is_saved
			)
			values
			(
			#project_coop_person_id#,
			#project_id#,
			#seq#,
			#name#,
			#company#,
			#education#,
			#contribute#,
			#birthday#,
			#gender#,
			#title#,
			#is_return#,
			#is_saved#
			)
			]]>
			<parameter name="project_coop_person_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="seq" type="int" />
			<parameter name="name" type="string" />
			<parameter name="company" type="string" />
			<parameter name="education" type="string" />
			<parameter name="contribute" type="string" />
			<parameter name="birthday" type="datetime" />
			<parameter name="gender" type="bit" />
			<parameter name="title" type="string" />
			<parameter name="is_return" type="bit" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_coop_org_by_id" resultType="table" allowPaging="true">
			<![CDATA[
			select
			a.project_coop_org_id as projectCoopOrgId,
			a.project_id as projectId,
			a.address,
			a.name,
			ifnull(b.`key`,'') as depId,
			a.contract,
			a.seq,
			a.telphone,
			a.zipcode as postcode,
			b.value as departmentValue
			from project_coop_org as a
			left join sys_code as b on a.dep_id=b.key and b.type=40
			where project_id = #project_id#
			order by a.seq
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_coop_org" resultType="table">
			<![CDATA[
			select
			a.project_coop_org_id as projectCoopOrgId,
			a.project_id as projectId,
			a.address,
			a.name,
			ifnull(b.`key`,'') as depId,
			a.contract,
			a.seq,
			a.telphone,
			a.zipcode as postcode,
			b.value as departmentValue
			from project_coop_org as a
			left join sys_code as b on a.dep_id=b.key and b.type=40
			where project_coop_org_id = #project_coop_org_id#
			]]>
			<parameter name="project_coop_org_id" type="string" />
		</statement>

		<statement name="find_coop_org_by_id_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_coop_org as a
			left join sys_code as b on a.dep_id=b.key and b.type=40
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_max_coop_org_seq_by_id" resultType="int">
			<![CDATA[
			SELECT 
			ifnull(max(seq),0) as maxSeq 
			FROM project_coop_org
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="update_coop_org">
			<![CDATA[
			update project_coop_org
			set project_coop_org_id=#project_coop_org_id#,
			address=#address#,
			name=#name#,
			dep_id=#dep_id#,
			contract=#contract#,
			telphone=#telphone#,
			zipcode=#postcode#
			where project_coop_org_id = #project_coop_org_id#
			]]>
			<parameter name="project_coop_org_id" type="string" />
			<parameter name="address" type="string" />
			<parameter name="name" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="contract" type="string" />
			<parameter name="telphone" type="string" />
			<parameter name="postcode" type="string" />
		</statement>

		<statement name="delete_coop_org">
			<![CDATA[
			delete from project_coop_org where project_coop_org_id = #project_coop_org_id#
			]]>
			<parameter name="project_coop_org_id" type="string" />
		</statement>

		<statement name="change_coop_org_order">
			<![CDATA[
			update  project_coop_org set seq=seq-1			
			where project_id = #project_id#
			and seq > #seq#			
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="seq" type="int" />
		</statement>

		<statement name="delete_coop_org_by_project_id">
			<![CDATA[
			delete from project_coop_org where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="reorder_coop_org" commandType="sp">
			<![CDATA[reorder_project_coop_org]]>
			<parameter name="project_coop_org_ids" type="string" />
			<parameter name="direction" type="int" />
		</statement>

		<statement name="create_coop_org">
			<![CDATA[
			insert into project_coop_org
			(
			project_coop_org_id,
			project_id,
			address,
			name,
			dep_id,
			contract,
			seq,
			telphone,
			zipcode
			)
			values
			(
			#project_coop_org_id#,
			#project_id#,
			#address#,
			#name#,
			#dep_id#,
			#contract#,
			#seq#,
			#telphone#,
			#postcode#
			)
			]]>
			<parameter name="project_coop_org_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="address" type="string" />
			<parameter name="name" type="string" />
			<parameter name="dep_id" type="string" />
			<parameter name="contract" type="string" />
			<parameter name="seq" type="int" />
			<parameter name="telphone" type="string" />
			<parameter name="postcode" type="string" />
		</statement>

		<statement name="find_cert_by_id" resultType="table">
			<![CDATA[
			select
			project_cert_id as projectCertId,
			project_id as projectId,
			`code`,
			future,
			files,
			report,
			`comment`,
			cert_code as certCode,
			cert_date as certDate
			from project_cert
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_cert">
			<![CDATA[
			delete from project_cert where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_cert">
			<![CDATA[
			insert into project_cert
			(
			project_cert_id,
			project_id,
			`code`,
			future,
			files,
			report,
			`comment`,
			cert_code,
			cert_date,
			is_saved
			)
			values
			(
			#project_cert_id#,
			#project_id#,
			#code#,
			#future#,
			#files#,
			#report#,
			#comment#,
			#cert_code#,
			#cert_date#,
			#is_saved#
			)
			]]>
			<parameter name="project_cert_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="code" type="string" />
			<parameter name="future" type="string" />
			<parameter name="files" type="string" />
			<parameter name="report" type="string" />
			<parameter name="comment" type="string" />
			<parameter name="cert_code" type="string" />
			<parameter name="cert_date" type="datetime" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="find_eval_committee_by_id" allowPaging="true" resultType="table">
			<![CDATA[
			select
			a.project_eval_committee_id as projectEvalCommitteeId,
			a.project_id as projectId,
			a.company,
			ifnull(b.`key`,'') as duty,
			a.name,
			a.seq,
			a.sex,
			ifnull(b1.`key`,'') as title,
			a.prof1,
			a.prof2,
			b.value as dutyValue,
			b1.value as titleValue
			from project_eval_committee as a
			left join sys_code as b on a.duty=b.key and b.type=37
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_id = #project_id#
			order by a.seq
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_eval_committee"  resultType="table">
			<![CDATA[
			select
			a.project_eval_committee_id as projectEvalCommitteeId,
			a.project_id as projectId,
			a.company,
			ifnull(b.`key`,'') as duty,
			a.name,
			a.seq,
			a.sex,
			ifnull(b1.`key`,'') as title,
			a.prof1,
			a.prof2,
			b.value as dutyValue,
			b1.value as titleValue
			from project_eval_committee as a
			left join sys_code as b on a.duty=b.key and b.type=37
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_eval_committee_id = #project_eval_committee_id#
			]]>
			<parameter name="project_eval_committee_id" type="string" />
		</statement>

		<statement name="find_eval_committee_by_id_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_eval_committee as a
			left join sys_code as b on a.duty=b.key and b.type=37
			left join sys_code as b1 on a.title=b1.key and b1.type=30
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_max_eval_committee_seq_by_id" resultType="int">
			<![CDATA[
			select
			ifnull(max(seq),0) as maxSeq
			from project_eval_committee 
			where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="update_eval_committee">
			<![CDATA[
			update project_eval_committee
			set project_eval_committee_id=#project_eval_committee_id#,
			company=#company#,
			duty=#duty#,
			name=#name#,
			sex=#sex#,
			title=#title#,
			prof1=#prof1#,
			prof2=#prof2#
			where project_eval_committee_id = #project_eval_committee_id#
			]]>
			<parameter name="project_eval_committee_id" type="string" />
			<parameter name="company" type="string" />
			<parameter name="duty" type="string" />
			<parameter name="name" type="string" />
			<parameter name="sex" type="bit" />
			<parameter name="title" type="string" />
			<parameter name="prof1" type="string" />
			<parameter name="prof2" type="string" />
		</statement>

		<statement name="delete_eval_committee">
			<![CDATA[
			delete from project_eval_committee where project_eval_committee_id = #project_eval_committee_id#
			]]>
			<parameter name="project_eval_committee_id" type="string" />
		</statement>

		<statement name="delete_eval_committee_by_project_id">
			<![CDATA[
			delete from project_eval_committee where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="change_eval_committee_order">
			<![CDATA[
			update  project_eval_committee set seq=seq-1			
			where project_id = #project_id#
			and seq > #seq#			
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="seq" type="int" />
		</statement>

		<statement name="reorder_eval_committee" commandType="sp">
			<![CDATA[reorder_priject_Eval_Committee]]>
			<parameter name="project_eval_committee_ids" type="string" />
			<parameter name="direction" type="int" />
		</statement>

		<statement name="create_eval_committee">
			<![CDATA[
			insert into project_eval_committee
			(
			project_eval_committee_id,
			project_id,
			company,
			duty,
			name,
			seq,
			sex,
			title,
			prof1,
			prof2
			)
			values
			(
			#project_eval_committee_id#,
			#project_id#,
			#company#,
			#duty#,
			#name#,
			#seq#,
			#sex#,
			#title#,
			#prof1#,
			#prof2#
			)
			]]>
			<parameter name="project_eval_committee_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="company" type="string" />
			<parameter name="duty" type="string" />
			<parameter name="name" type="string" />
			<parameter name="seq" type="int" />
			<parameter name="sex" type="bit" />
			<parameter name="title" type="string" />
			<parameter name="prof1" type="string" />
			<parameter name="prof2" type="string" />
		</statement>

		<statement name="validate_organization" resultType="bool">
			<![CDATA[
			select exists(
			select `name` from organization
			where `name` = #name#)
			]]>
			<parameter name="name" type="string" />
		</statement>

		<statement name="find_org_info_by_name" resultType="string">
			<![CDATA[
			select company_id from organization
			where `name` = #name#
			]]>
			<parameter name="name" type="string" />
		</statement>

		<statement name="create_organization">
			<![CDATA[
			 insert into organization
			(
			company_id,
			`name`,
			`code`,
			address,
			telephone,
			website,
			postcode,
			faxcode,
			`type`,
			property,
			province,
			parent,
			contract_name,
			contract_telephone,
			contract_email,
			research_range,
			research_subject,
			advantage_range,
			advantage_subject,
			company_summary,
			advantage_summary,
			is_sciencecompany
			)
			values
			(
			#company_id#,
			#name#,
			#code#,
			#address#,
			#telephone#,
			#website#,
			#postcode#,
			#faxcode#,
			#type#,
			#property#,
			#province#,
			#parent#,
			#contract_name#,
			#contract_telephone#,
			#contract_email#,
			#research_range#,
			#research_subject#,
			#advantage_range#,
			#advantage_subject#,
			#company_summary#,
			#advantage_summary#,
			#is_sciencecompany#
			)
			]]>
			<parameter name="company_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="code" type="string" />
			<parameter name="address" type="string" />
			<parameter name="telephone" type="string" />
			<parameter name="website" type="string" />
			<parameter name="postcode" type="string" />
			<parameter name="faxcode" type="string" />
			<parameter name="type" type="string" />
			<parameter name="property" type="string" />
			<parameter name="province" type="string" />
			<parameter name="parent" type="string" />
			<parameter name="contract_name" type="string" />
			<parameter name="contract_telephone" type="string" />
			<parameter name="contract_email" type="string" />
			<parameter name="research_range" type="string" />
			<parameter name="research_subject" type="string" />
			<parameter name="advantage_range" type="string" />
			<parameter name="advantage_subject" type="string" />
			<parameter name="company_summary" type="string" />
			<parameter name="advantage_summary" type="string" />
			<parameter name="is_sciencecompany" type="bit" />
		</statement>

		<statement name="update_organization">
			<![CDATA[
			 update organization
			 set `name`=#name#,
			`code`=#code#,
			address=#address#,
			telephone=#telephone#,
			website=#website#,
			postcode=#postcode#,
			faxcode=#faxcode#,
			`type`=#type#,
			property=#property#,
			province=#province#,
			parent=#parent#,
			contract_name=#contract_name#,
			contract_telephone=#contract_telephone#,
			contract_email=#contract_email#,
			research_range=#research_range#,
			research_subject=#research_subject#,
			advantage_range=#advantage_range#,
			advantage_subject=#advantage_subject#,
			company_summary=#company_summary#,
			advantage_summary=#advantage_summary#,
			is_sciencecompany=#is_sciencecompany#
			where company_id=#company_id#
			]]>
			<parameter name="company_id" type="string" />
			<parameter name="name" type="string" />
			<parameter name="code" type="string" />
			<parameter name="address" type="string" />
			<parameter name="telephone" type="string" />
			<parameter name="website" type="string" />
			<parameter name="postcode" type="string" />
			<parameter name="faxcode" type="string" />
			<parameter name="type" type="string" />
			<parameter name="property" type="string" />
			<parameter name="province" type="string" />
			<parameter name="parent" type="string" />
			<parameter name="contract_name" type="string" />
			<parameter name="contract_telephone" type="string" />
			<parameter name="contract_email" type="string" />
			<parameter name="research_range" type="string" />
			<parameter name="research_subject" type="string" />
			<parameter name="advantage_range" type="string" />
			<parameter name="advantage_subject" type="string" />
			<parameter name="company_summary" type="string" />
			<parameter name="advantage_summary" type="string" />
			<parameter name="is_sciencecompany" type="bit" />
		</statement>

		<statement name="exists_project_iprs" resultType="bool">
			<![CDATA[
			select exists(select project_id from project_iprs where project_id = #project_id#);
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_project_iprs" resultType="table">
			<![CDATA[
			select
			project_id as projectId,
			project_iprs_id as projectIprsId,
			`style`,
			authcode,
			authname,
			patent_unok_count as patentUnOkCount,
			patent_ok_count as patentOkCount
			from project_iprs
			where project_id = #project_id# 
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="create_project_iprs">
			<![CDATA[
			insert into project_iprs
			(
			project_iprs_id,
			project_id,
			style,
			authcode,
			authname,
			patent_unok_count,
			patent_ok_count,
			is_saved
			)
			values
			(
			#project_iprs_id#,
			#project_id#,
			#style#,
			#authcode#,
			#authname#,
			#patent_unok_count#,
			#patent_ok_count#,
			#is_saved#
			)
			]]>
			<parameter name="project_iprs_id" type="string" />
			<parameter name="project_id" type="string" />
			<parameter name="style" type="string" />
			<parameter name="authcode" type="string" />
			<parameter name="authname" type="string" />
			<parameter name="patent_unok_count" type="int" />
			<parameter name="patent_ok_count" type="int" />
			<parameter name="is_saved" type="bit"/>
		</statement>

		<statement name="update_project_iprs_basic_info">
			<![CDATA[
			update project_iprs
			set style=#style#,
			authcode=#authcode#,
			authname=#authname#
			where project_id= #project_id#
			]]>
			<parameter name="project_id" type="string" />
			<parameter name="style" type="string" />
			<parameter name="authcode" type="string" />
			<parameter name="authname" type="string" />
		</statement>

		<statement name="delete_project_iprs">
			<![CDATA[
			delete from project_patent where project_iprs_id in (select project_iprs_id from project_iprs where project_id = #project_id#);
			delete from project_iprs where project_id = #project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="update_project_iprs_count_info">
			<![CDATA[
			update project_iprs as pi
			set pi.patent_unok_count=(select count(*) from project_patent  where state = 1 and project_patent.project_iprs_id= #project_iprs_id#)
			where pi.project_iprs_id= #project_iprs_id#;
			update project_iprs as pi
			set pi.patent_ok_count=(select count(*) from project_patent  where state = 2 and project_patent.project_iprs_id= #project_iprs_id#)
			where pi.project_iprs_id= #project_iprs_id#;
			]]>
			<parameter name="project_iprs_id" type="string" />
		</statement>

		<statement name="find_project_patent" allowPaging="true" resultType="table">
			<![CDATA[
			select
			p.project_patent_id as projectPatentId,
			p.project_iprs_id as projectIprsId,
			p.seq,
			p.style,
			p.state,
			p.code,
			p.name
			from project_patent as p join project_iprs as q 
			where p.project_iprs_id = q.project_iprs_id
			and q.project_id=#project_id#
			order by convert(p.name using gbk)
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="find_patent_by_id"  resultType="table">
			<![CDATA[
			select
			project_patent_id as projectPatentId,
			project_iprs_id as projectIprsId,
			seq,
			style,
			state,
			code,
			name
			from project_patent  
			where project_patent_id = #project_patent_id#
			]]>
			<parameter name="project_patent_id" type="string" />
		</statement>

		<statement name="find_project_patent_count" resultType="int">
			<![CDATA[
			select
			count(*)
			from project_patent as p join project_iprs as q 
			where p.project_iprs_id = q.project_iprs_id
			and q.project_id=#project_id#
			]]>
			<parameter name="project_id" type="string" />
		</statement>

		<statement name="delete_project_patent">
			<![CDATA[
			delete from project_patent where project_patent_id = #project_patent_id#
			]]>
			<parameter name="project_patent_id" type="string" />
		</statement>

		<statement name="delete_project_patent_by_iprs_id">
			<![CDATA[
			delete from project_patent where project_iprs_id = #project_iprs_id#
			]]>
			<parameter name="project_iprs_id" type="string" />
		</statement>

		<statement name="create_project_patent">
			<![CDATA[
			insert into project_patent
			(
			project_patent_id ,
			project_iprs_id ,
			seq,
			style,
			state,
			code,
			name
			)
			value
			(
			#project_patent_id#,
			#project_iprs_id#,
			#seq#,
			#style#,
			#state#,
			#code#,
			#name#
			)
			]]>
			<parameter name="project_patent_id" type="string" />
			<parameter name="project_iprs_id" type="string" />
			<parameter name="seq" type="int" />
			<parameter name="style" type="string" />
			<parameter name="state" type="string" />
			<parameter name="code" type="string" />
			<parameter name="name" type="string" />
		</statement>

		<statement name="find_project_sum_by_year" resultType="int">
			<![CDATA[
			select 
			count(*)
			from project 
			where `status`=4
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="year">
					<![CDATA[
					year=#year#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="year" type="int" />
		</statement>

		<statement name="find_project_sum_by_year_and_dep" resultType="table">
			<![CDATA[
			select 
			p.dep_id as depId,
			s.`value` as depName,
			count(*) as count
			from 
			project as p left join sys_code as s on p.dep_id = s.key and s.type=40
			where `status`=4
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="year">
					<![CDATA[
					p.year=#year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					p.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by depId,depName
            ]]>
			<parameter name="year" type="int" />
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_project_sum_by_year_and_projecttype" resultType="table">
			<![CDATA[
			select 
			`type`,
			case `type` 
			when '1' then '应用技术类'
			when '2' then '软科学研究'
			when '3' then '基础理论类'
			end  as  typeValue,
			count(*) as count
			from 
			project  
			where `status`=4
            ]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					year<=#max_year#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by type
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
		</statement>

		<statement name="find_project_sum_by_year_projecttype_and_dep" resultType="table">
			<![CDATA[
			select 
			p.`type`,
			p.dep_id as depId,
			s.`value` as depName,
			case p.`type` 
			when '1' then '应用技术成果'
			when '2' then '基础理论成果'
			when '3' then '软科学研究成果'
			end  as  typeValue,
			count(*) as count
			from 
			(select `year`,dep_id,`type` from project where `status` =4) as  p left join sys_code as s
			on p.dep_id = s.key and s.type=40 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					p.year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					p.year<=#max_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					p.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by depId,depName,type
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_project_sum_by_year_and_source" resultType="table">
			<![CDATA[
			select 
			ifnull(s.`key`,'') as projectSource,
			s.`value` as sourceValue,
			count(*) as count
			from 
			project as p 
			left join project_establish as pe on p.project_id =  pe.project_id and p.`status`=4
			left join sys_code as s	on pe.source = s.key and s.type=38 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					p.year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					p.year<=#max_year#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by pe.source,s.value
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
		</statement>

		<statement name="find_project_sum_by_year_dep_and_source" resultType="table">
			<![CDATA[
			select 
			ifnull(s.`key`,'') as projectSource,
			s.`value` as sourceValue,
			s1.`value` as depValue,
			ifnull(s1.`key`,'') as depId,
			count(*) as count
			from 
			project as p 
			join project_establish as pe on p.project_id =  pe.project_id and p.`status`=4
			left join sys_code as s	on pe.source = s.`key` and s.`type`=38 
			left join sys_code as s1 on p.dep_id = s1.`key` and s1.`type`=40
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					p.year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					p.year<=#max_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					p.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by pe.`source`,s.`key`,s1.`key`
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_project_sum_by_year_and_entitytype" resultType="table">
			<![CDATA[
			SELECT 
			entityTypeValue,
			count(*) as count
			FROM entitysumtemp 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					projectYear>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					projectYear<=#max_year#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by entityTypeValue
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
		</statement>

		<statement name="find_project_sum_by_year_dep_and_entitytype" resultType="table">
			<![CDATA[
			SELECT
			entityTypeValue,
			depId,
			depName,
			count(*) as count
			FROM entitysumtemp
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					projectYear>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					projectYear<=#max_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					depId=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
            group by entityTypeValue,depId,depName
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_project_sum_by_year_and_moneysource" resultType="table">
			<![CDATA[
			select 
			ifnull(sum(ifnull(pe.nation,0.0)),0) as '国家投入',
			ifnull(sum(ifnull(pe.ministry,0.0)),0) as '部门投入',
			ifnull(sum(ifnull(pe.local_all,0.0)),0) as '地方投入',
			ifnull(sum(ifnull(pe.fund,0.0)),0) as '基金投入',
			ifnull(sum(ifnull(pe.self_owen,0.0)),0) as '自有资金',
			ifnull(sum(ifnull(pe.loan,0.0)),0) as '银行贷款',
			ifnull(sum(ifnull(pe.outside,0.0)),0) as '国外资金',
			ifnull(sum(ifnull(pe.other,0.0)),0) as '其他'
			from project as p
			left join  project_establish pe on p.project_id = pe.project_id and p.`status`=4 
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					year<=#max_year#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
		</statement>

		<statement name="find_project_sum_by_year_dep_and_moneysource" resultType="table">
			<![CDATA[
			select 
			p.dep_id as depId,
			s.`value` as depName,
			ifnull(sum(ifnull(pe.nation,0.0)),0) as '国家投入',
			ifnull(sum(ifnull(pe.ministry,0.0)),0) as '部门投入',
			ifnull(sum(ifnull(pe.local_all,0.0)),0) as '地方投入',
			ifnull(sum(ifnull(pe.fund,0.0)),0) as '基金投入',
			ifnull(sum(ifnull(pe.self_owen,0.0)),0) as '自有资金',
			ifnull(sum(ifnull(pe.loan,0.0)),0) as '银行贷款',
			ifnull(sum(ifnull(pe.outside,0.0)),0) as '国外资金',
			ifnull(sum(ifnull(pe.other,0.0)),0) as '其他'
			from project as p
			join  project_establish pe on p.project_id = pe.project_id and p.`status`=4 
			left join sys_code s on p.dep_id = s.`key` and s.`type`=40
			]]>
			<dynamic prepend="where">
				<isNotEmpty parameter="min_year">
					<![CDATA[
					p.year>=#min_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="max_year">
					<![CDATA[
					p.year<=#max_year#
					]]>
				</isNotEmpty>
				<isNotEmpty parameter="dep_id">
					<![CDATA[
					p.dep_id=#dep_id#
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[
			group by p.dep_id,s.`value`
            ]]>
			<parameter name="min_year" type="int" />
			<parameter name="max_year" type="int" />
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_ok_projects" resultType="table">
			<![CDATA[
			select 
			`name` as projectName,
			project_id as projectId
			from 
			project
			where `status`=4 and dep_id=#dep_id#
			order by `name`
			]]>
			<parameter name ="dep_id" type="string"/>
		</statement>

		<statement name="find_ok_projects_by_year" allowPaging="true" resultType="table">
			<![CDATA[
			SELECT 
			p.project_id as projectId,
			p.`name` as projectName,
			org.`name` as ownerName,
			p.`year` as projectYear
			FROM project as p
			left join project_org  as org on p.project_id = org.project_id 
			where p.`status`=4			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="year">
					<![CDATA[
					p.year=#year#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name ="year" type="string"/>
		</statement>

		<statement name="find_ok_projects_by_year_count"  resultType="int">
			<![CDATA[
			SELECT 
			count(*)
			FROM project as p
			left join project_org  as org on p.project_id = org.project_id 
			where p.`status`=4			
			]]>
			<dynamic prepend="and">
				<isNotEmpty parameter="year">
					<![CDATA[
					p.year=#year#
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name ="year" type="string"/>
		</statement>

		<statement name="find_project_info_by_project_id" resultType="table">
			<![CDATA[
			select
			p.`year` as projectYear,
			p.secret,
			'' as c1,
			p.index_key3 as indexKey3,
			p.index_key4 as indexKey4,
			p.approve_code as approveCode,
			p.approve_date as approveDate,
			p.style as style1,
			p.technology ,
			p.industry,
			p.property as property,
			pe.`source` as establishSource,
		    pb.summary as briefSummary,
			pb.content as briefContent,
			cert.future as certFuture,
			cert.files as certFiles,
			p.primary_key1 as primaryKey1,
			p.primary_key2 as primaryKey2,
			p.primary_key3 as primaryKey3,
			p.stage ,
			p.`level`,
			p.purpose,
			p.style as style2,
			p.`name` as name,
			p.transfer,
			p.project_id as projectId,
			org.`code` as orgCode,			
			p.start_date as startDate,
			p.end_date as endDate,
			pe.number as establishNumber,
			p.research,
			org.address as orgAddress,
			org.postcode as orgPostcode,
			org.telephone as orgTelephone,
			org.faxcode as orgFaxcode,
			org.contract_name as orgContractName,
			'' as c5_1,
			org.contract_telephone as orgContractTelephone,
			org.property as orgProperty,
			org.website as orgWebsite,
			org.contract_email as orgContractEmail,
			org.`type` as orgType,
			org.parent as orgParent,
			org.province as orgProvince,
			org.`name` as orgName,
			pa.stage as applyStage,
			pa.reason as applyReason,
			eval.date as c6_1,			
			pa.transfer_count as applyTransferCount,
			pa.transfer_budget_invest as applyTransferBudgetInvest,
			pa.transfer_budget_benifit as applyTransferBudgetBenifit,
			'' as c6_2,
			cert.`code` as certCode,
			cert.cert_date as certDate,			
			cert.`comment` as certComment,
			pa.reason_detail as applyReasonDetail,
			'' as c6_3,
			'' as c6_4,
			'' as c6_5,
			pa.transfer_style as applyTransferStyle,
			org.is_sciencecompany as c8,
			'' as c9,
			'' as c10,
			'' as c11_1,
			'' as c11_2,
			pe.nation_project as c11,		
			pe.local_all as establishLocalAll,
			pe.fund as establishFund,
			pe.nation as establishNation,
			pa.intellectual_property_all_money as applyIntellectualPropertyAllMoney,
			eval.company as evalCompany,
			cert.report as certReport,
			eval.method as evalMethod,
			cert.`code` as evalCode,
			'' as c11_3,
			iprs.style as iprsStyle,
			iprs.authname as iprsAuthName,
			iprs.authcode as iprsAuthCode,
			'' as c15,
			'' as c16_1,
			'' as c16_2,
			'' as c16_3,
			p.`type` as type,
			pe.self_owen as establishSelfOwen,
			pe.other as establishOther,
			pe.all_money establishAllMoney,
			'' as c17,
			p.recommend_company as recommendCompany,
			p.index_key1 as indexKey1,
			p.index_key2 as indexKey2,
			pe.province as establishProvince,
			pe.city as establishCity,
			pe.country as establishCountry,
			'' as c18_1,
			pe.ministry as establishMinistry,
			pe.loan as establishLoan,
			pe.outside as establishOutside,
			'' as c19,
			iprs.patent_unok_count as iprsUnOkCount,
			iprs.patent_ok_count as iprsOkCount,
			'' as c22,
			'' as c23,
			pe.`name` as establishName,
			'' as c27,
			'' as c25,
			(select  group_concat(distinct pp.style) from project_patent as pp where pp.project_iprs_id=iprs.project_iprs_id) as c26,
			org.transfer_name as orgTransferName,
			org.transfer_telephone as orgTransferTelephone,
			org.transfer_email as orgTransferEmail
			from project as p
			left join project_org as org on p.project_id = org.project_id
			left join project_establish as pe on p.project_id = pe.project_id
			left join project_brief as pb on p.project_id = pb.project_id
			left join project_iprs as iprs on p.project_id =iprs.project_id
			left join project_cert as cert on p.project_id =cert.project_id
			left join project_eval as eval on p.project_id = eval.project_id
			left join project_apply as pa on p.project_id = pa.project_id
			where p.project_id=#project_id#
			]]>
			<parameter name ="project_id" type="string"/>
		</statement>

		<statement name="change_project_info_is_saved_ok">
			<![CDATA[
			update project set is_saved=1 where project_id=#project_id#;
			update project_org set is_saved=1 where project_id=#project_id#;
			update project_establish set is_saved=1 where project_id=#project_id#;
			update project_iprs set is_saved=1 where project_id=#project_id#;
			update project_bonus set is_saved=1 where project_id=#project_id#;
			update project_eval set is_saved=1 where project_id=#project_id#;
			update project_brief set is_saved=1 where project_id=#project_id#;
			update project_cert set is_saved=1 where project_id=#project_id#;	
			update project_coop_person set is_saved=1 where project_id=#project_id#;	
			]]>
			<parameter name ="project_id" type="string"/>
		</statement>

		<statement name="change_project_basic_info">
			<![CDATA[
		   update project set dep_id=#dep_id#,`name`=#name#,`year`=#year# where project_id=#project_id#;
		   ]]>
			<parameter name ="project_id" type="string"/>
			<parameter name ="dep_id" type="string"/>
			<parameter name="name" type="string"/>
			<parameter name="year" type="int"/>
		</statement>

	</module>
</root>
